![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
Our next step is to connect to the database that will process the users query, as shown in Listing 4.6.
Listing 4.6 Opening a database connection.
if (evt.target == ConnectBtn) { // If the user clicks the "Connect" button, connect to the database // specified in the DBurl TextArea and the user name specified in the // NameField TextArea. url=DBurl.getText(); Name=NameField.getText(); try { new imaginary.sql.iMsqlDriver(); // This creates a new instance of the Driver we want to use. There are a // number of ways to specify which driver you want to use, and there is // even a way to let the JDBC DriverManager choose which driver it thinks // it needs to connect to the data source. con = DriverManager.getConnection(url, Name, ""); // Actually make the connection. Use the entered URL and the entered // user name when making the connection. We haven't specified a password, // so just send nothing (""). ConnectBtn.setLabel("Reconnect to Database"); // Finally, change what the ConnectBtn to show "Reconnect to Database". } catch( Exception e ) { e.printStackTrace(); OutputField.setText(e.getMessage()); } // The creation of the connection throws an exception if there was a // problem connecting using the specified parameters. We have to enclose // the getConnection method in a try-catch block to catch any // exceptions that may be thrown. If there is a problem and an exception // thrown, print it out to the console, and to the OutputField. return true; } return false; } // handleEvent() end
Now that we have opened the connection to the data source (Listing 4.6), its time to set up the mechanism for executing queries and getting the results, as shown in Listings 4.7 and 4.8. The parameter that we need in this method is a String containing the SQL query the user entered into the QueryField. We will return the results of the query as a string because we only want to pipe all of the results into the OutputField TextArea. We cast all of the returned results into a Stringhowever, if the database contains binary data, we could get some weird output, or even cause the program to break. When I tested the applet, the data source that I queried contained numerical and strings only. In Chapter 7, Ill show you how to deal with different data types in the ANSI SQL-2 specification, upon which the data types for the JDBC are based.
Listing 4.7 Executing a statement.
public String Select(String QueryLine) { // This is the method we called above in Listing 4.5. // We return a String, and use a String parameter for the entered query. String Output=""; int columns; int pos; try { // Several of the following methods can throw exceptions if there was a // problem with the query, or if the connection breaks, or if // we improperly try to retrieve results. Statement stmt = con.createStatement(); // First, we instantiate a Statement class that is required to execute // the query. The Connection class returns a Statement object in its // createStatement method, which links the opened connection to // the passed-back Statement object. This is how the stmt instance // is linked to the actual connection to the data source. ResultSet rs = stmt.executeQuery(QueryLine); // The ResultSet in turn is linked to the connection to the data source // via the Statement class. The Statement class contains the executeQuery // method, which returns a ResultSet class. This is analagous to a // pointer that can be used to retrieve the results from the JDBC // connection. columns=(rs.getMetaData()).getColumnCount(); // Here we use the getMetaData method in the result set to return a // Metadata object. The MetaData object contains a getColumnCount // method which we use to determine how many columns of data // are present in the result. We set this equal to an integer // variable.
Listing 4.8 Getting the Result and MetaData Information.
while(rs.next()) { // Now, we use the next method of the ResultSet instance rs to fetch // each row, one by one. There are more optimized ways of doing // this--namely using the inputStream feature of the JDBC driver. // I show you an example of this in Chapter 9. for( pos=1; pos<=columns; pos++) { // Now let's get each column in the row ( each cell ), one by one. Output+=rs.getObject(pos)+" "; // Here we've used the general method for getting a result. The // getObject method will attempt to caste the result in the form // of its assignee, in this case the String variable Output. // We simply get each "cell" and add a space to it, then append it onto // the Output variable. } // End for loop (end looping through the columns for a specific row ). Output+="\n"; // For each row that we fetch, we need to add a carriage return so that // the next fetched row starts on the next line. } // End while loop ( end fetching rows when no more rows are left ). stmt.close(); // Clean up, close the stmt, in effect, close the input-output query // connection streams, but stay connected to the data source. } catch( Exception e ) { e.printStackTrace(); Output=e.getMessage(); } // We have to catch any exceptions that were thrown while we were // querying or retrieving the data. Print the exception // to the console and return it so it can be shown to the user // in the applet. return Output; // Before exiting, return the result that we got. }
The last part of the applet, shown in Listing 4.9, involves terminating the connection to the data source. This is done in the destroy method of the applet. We have to catch an exception, if one occurs, while the close method is called on the connection.
Listing 4.9 Terminating the connection.
public void destroy() { try {con.close();} catch( Exception e ) { e.printStackTrace(); System.out.println(e.getMessage()); } } // end destroy } // end applet IQ
Previous | Table of Contents | Next |